package com.apes.fn.rpt.capital;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;

/**
 * @author pengyb
 */
@Component
public class CapitalReportDao extends ApesDao {

    @Autowired
    EntityManager entityManager;

    /**
     * 分页包装
     *
     * @param sSQL
     * @return
     */
    private String contactPageSQL(String sSQL) {
        String contactSQL = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( ";
        contactSQL += sSQL;
        contactSQL += " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return contactSQL;
    }

    /**
     * 取总行数
     *
     * @param sSQL
     * @param param
     * @return
     */
    private String countSQL(String sSQL, JSONObject param) {

        String contactSQL = "SELECT COUNT(1) COUNT FROM (" + sSQL + ") X WHERE 1 = 1";
        contactSQL += param.getString("SQL");
        return contactSQL;
    }

    /**
     * 收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray queryPaymentMethod(JSONObject param) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  PAYMENT_METHOD_TYPE TYPE_ID,\n" +
                "       NVL(SUM(AMOUNT), 0)  AMOUNT,\n" +
                "       PAYMENT_METHOD_TYPE_NAME TYPE\n" +
                "FROM (SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             B.PAYMENT_METHOD_TYPE,\n" +
                "             (SELECT T.NAME\n" +
                "              FROM MAS_PAYMENT_METHOD T\n" +
                "              WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             B.AMOUNT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID AND (C.CLAIM_TYPE IS NULL OR (C.CLAIM_TYPE IS NOT NULL  AND B.PAYMENT_METHOD_TYPE<>'09')) \n" +
                "      UNION ALL\n" +
                "      SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             B.AMOUNT\n" +
                "      FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "      UNION ALL\n" +
                "      SELECT A.CREATE_DATE DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             '09'          PAYMENT_METHOD_TYPE,\n" +
                "             '其他应收'        PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.COMPANY_ID,\n" +
                "             B.PRICE_TOTAL AMOUNT\n" +
                "      FROM ACC_VOUCHER a,\n" +
                "           ACC_VOUCHER_ITEM B\n" +
                "      WHERE A.ID = B.VOUCHER_ID\n" +
                "        AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "        AND a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss'\n" +
                "        AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.COMPANY_ID)\n" +
                "          != (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.PARTNER_ID))\n" +
                "      UNION ALL\n" +
                "      SELECT A.CREATE_DATE DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             '09'          PAYMENT_METHOD_TYPE,\n" +
                "             '其他应收'        PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.PAYMENT_ID  COMPANY_ID,\n" +
                "             A.AMOUNT      AMOUNT\n" +
                "      FROM FN_SETTLE_EXPENSE A\n" +
                "      WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "        AND A.TYPE = 'in'\n" +
                "\n" +
                "      UNION ALL\n" +
                "      SELECT C.APPROVE_DATE  DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             E.PAYMENT_METHOD_TYPE,\n" +
                "             M.name          PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             E.INCOME_AMOUNT AMOUNT\n" +
                "      from (SELECT A.ID DEPT_ID,\n" +
                "                   B.ID IMCOME_ID,\n" +
                "                   B.APPROVE_DATE,\n" +
                "                   B.COMPANY_ID\n" +
                "            FROM MAS_DEPT A,\n" +
                "                 MONEY_INCOME B\n" +
                "            WHERE A.COMPANY_ID\n" +
                "                = B.COMPANY_ID\n" +
                "              AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "              AND B.STATE = 'approve') C,\n" +
                "           MONEY_INCOME_ITEM E,\n" +
                "           Mas_Payment_Method M\n" +
                "      WHERE C.IMCOME_ID = E.MONEY_INCOME\n" +
                "        AND E.PAYMENT_METHOD_TYPE = M.ID\n" +
                "\n" +
                "      UNION ALL\n" +
                "      SELECT A.CREATE_DATE   DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             '09'            PAYMENT_METHOD_TYPE,\n" +
                "             '其他应收'          PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.COMPANY_ID    COMPANY_ID,\n" +
                "             B.FN_COMMISSION AMOUNT\n" +
                "      FROM POLICY_INFORMATION A,\n" +
                "           POLICY_INFORMATION_ITEM B\n" +
                "      WHERE A.ID = B.POLICY_INFORMATION_ID\n" +
                "\n" +
                "      UNION ALL\n" +
                "      SELECT A.DATE_APPROVE DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             '09'           PAYMENT_METHOD_TYPE,\n" +
                "             '其他应收'         PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.COMPANY_ID   COMPANY_ID,\n" +
                "             A.STORE_PRICE  AMOUNT\n" +
                "      FROM WAR_USED_CAR A,\n" +
                "           MAS_DEPT B\n" +
                "      WHERE A.DEPT_ID = B.ID\n" +
                "        AND A.STATE = 'approve'\n" +
                "     )");
        sb.append(" WHERE 1 = 1  AND PAYMENT_METHOD_TYPE NOT IN ('10', '12','05')");
        if (!param.containsKey("DATE_START") || !param.containsKey("DATE_END")) {
            throw new RuntimeException("日期为必输项！");
        }
        sb.append("  AND DATE_APPROVE >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "  AND DATE_APPROVE < to_date(:DATE_END, 'yyyy-MM-dd') ");

        if (param.containsKey("DEPT_ID") && param.getString("DEPT_ID") != null) {
            sb.append("AND DEPT_ID = :DEPT_ID ");
        }
        if (param.containsKey("COMPANY_ID") && param.getString("COMPANY_ID") != null) {
            sb.append("AND COMPANY_ID = :COMPANY_ID ");
        }
        sb.append("  AND ((EXISTS(SELECT 1\n" +
                "               FROM AUT_POSTS ROLE1,\n" +
                "                    AUT_LOCAL_PERMIT ROLE2,\n" +
                "                    AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
                "               WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID\n" +
                "                         AND ROLE1.ID = :ROLE AND ROLE3.TYPE = 'pt_dept' AND ROLE3.PURPOSE = 'pp_search' AND DEPT_ID =\n" +
                "                     ROLE3.ORG_ID\n" +
                "            ) OR (SELECT NVL(COUNT(1), 0)\n" +
                "                  FROM AUT_POSTS ROLE1,\n" +
                "                       AUT_LOCAL_PERMIT ROLE2,\n" +
                "                       AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
                "                  WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID\n" +
                "                    AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID\n" +
                "                    AND ROLE1.ID\n" +
                "                      = :ROLE\n" +
                "                    AND ROLE3.TYPE = 'pt_dept'\n" +
                "                    AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(" GROUP BY PAYMENT_METHOD_TYPE,PAYMENT_METHOD_TYPE_NAME\n" +
                "ORDER BY PAYMENT_METHOD_TYPE");
        return toJSONArray(find(sb.toString(), param));
    }


    /**
     * 每日营业报表
     *
     * @param param
     * @return
     */
    public JSONArray queryDailySalesReport(JSONObject param) {
        StringBuffer sb = new StringBuffer();
        sb.append("        SELECT " +
                "            SUM(SALE_MONEY) SALE_MONEY, SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "            SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,\n" +
                "            SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT,\n" +
                "            SUM(YEARCARD) YEARCARD, SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT,\n" +
                "            SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD) BUY_YEARCARD,SUM(BUY_BEAUTY_YEAR_CARD) BUY_BEAUTY_YEAR_CARD,SUM(BUY_MAINTAIN_YEAR_CARD) BUY_MAINTAIN_YEAR_CARD,\n" +
                "            SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE,\n" +
                "            SUM(ADVANCE_PAYMENT_RECHARGE) ADVANCE_PAYMENT_RECHARGE,\n" +
                "            SUM(FIXED_LOSS_COLLECTION) FIXED_LOSS_COLLECTION,\n" +
                "            SUM(FIXED_LOSS_DIRECT_COMPENSATION) FIXED_LOSS_DIRECT_COMPENSATION,\n" +
                "            SUM(CAPITAL_INCOME) CAPITAL_INCOME,\n" +
                "            SUM(SERVICE_PERFORMANCE) SERVICE_PERFORMANCE,\n" +
                "            SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "            SUM(CAR_WASH) CAR_WASH, SUM(CAR_WASH_TIMES) CAR_WASH_TIMES,\n" +
                "            SUM(COSMETOLOGY) COSMETOLOGY,\n" +
                "            SUM(COSMETOLOGY_TIMES) COSMETOLOGY_TIMES, SUM(MAINTAIN) MAINTAIN,\n" +
                "            SUM(MAINTAIN_TIMES) MAINTAIN_TIMES, SUM(SMALL_MAINTAIN) SMALL_MAINTAIN,\n" +
                "            SUM(SMALL_MAINTAIN_TIMES) SMALL_MAINTAIN_TIMES, SUM(TYRE) TYRE,\n" +
                "            SUM(TYRE_TIMES) TYRE_TIMES, SUM(REPAIR) REPAIR,\n" +
                "            SUM(REPAIR_TIMES) REPAIR_TIMES, SUM(SHEET_METAL_SPRAY) SHEET_METAL_SPRAY,\n" +
                "            SUM(SHEET_METAL_SPRAY_TIMES) SHEET_METAL_SPRAY_TIMES,\n" +
                "            SUM(REFIT) REFIT, SUM(REFIT_TIMES) REFIT_TIMES,\n" +
                "            SUM(ROAD_RESCUE) ROAD_RESCUE,SUM(INSURANCE_ORDER_AMT) INSURANCE_ORDER_AMT,\n" +
                "            SUM(ROAD_RESCUE_TIMES) ROAD_RESCUE_TIMES, SUM(BOUTIQUE) BOUTIQUE,\n" +
                "            SUM(ARTICLES_FOR_DAILY_USE) ARTICLES_FOR_DAILY_USE,\n" +
                "            SUM(TECHNICAL_INSPECTION) TECHNICAL_INSPECTION,\n" +
                "            SUM(INFORMATION_SERVICE) INFORMATION_SERVICE,SUM(STORED_VALUE_RETAIL) STORED_VALUE_RETAIL,SUM(E_MAINTAIN) E_MAINTAIN,\n" +
                "            SUM(USER_CAR_FEE) USER_CAR_FEE,SUM(FN_COMMISSION) FN_COMMISSION,\n" +
                "            SUM(FN_SETTLE_EXPENS) FN_SETTLE_EXPENS,\n" +
                "            NVL(SUM(CROSS_COMPANY_INCOME), 0) CROSS_COMPANY_INCOME ,NVL(SUM(CROSS_COMPANY_PAY), 0) CROSS_COMPANY_PAY ,NVL(SUM(SPRAY_PAINT_NUMBER), 0) SPRAY_PAINT_NUMBER ,NVL(SUM(ANNUAL_AUDIT), 0) ANNUAL_AUDIT ,\n" +
                "            SUM(OUR_LOSS_COLLECTION) OUR_LOSS_COLLECTION,\n" +
                "            SUM(INNER_LOSS_COLLECTION) INNER_LOSS_COLLECTION,\n" +
                "            SUM(SERVICE_PERFORMANCE_MONEY) SERVICE_PERFORMANCE_MONEY\n" +
                "        FROM (    SELECT \n" +
                "              SALE_MONEY, PAYMENT_MONEY,\n" +
                "              ACCOUNTS_RECEIVABLEIN, ACCOUNTS_RECEIVABLEOUT, YEARCARD*-1 YEARCARD,\n" +
                "              ADVANCE_PAYMENT*-1 ADVANCE_PAYMENT, STORED_VALUE*-1 STORED_VALUE,\n" +
                "              BUY_YEARCARD,BUY_BEAUTY_YEAR_CARD,BUY_MAINTAIN_YEAR_CARD, STORED_VALUE_RECHARGE,\n" +
                "              ADVANCE_PAYMENT_RECHARGE, FIXED_LOSS_COLLECTION,\n" +
                "              FIXED_LOSS_DIRECT_COMPENSATION, CAPITAL_INCOME, SERVICE_PERFORMANCE,\n" +
                "              TIMES_OF_ENTERING_THE_STORE, CAR_WASH, CAR_WASH_TIMES, COSMETOLOGY,\n" +
                "              COSMETOLOGY_TIMES, MAINTAIN, MAINTAIN_TIMES,SMALL_MAINTAIN, SMALL_MAINTAIN_TIMES, TYRE, TYRE_TIMES, REPAIR,\n" +
                "              REPAIR_TIMES, SHEET_METAL_SPRAY, SHEET_METAL_SPRAY_TIMES, REFIT,\n" +
                "              REFIT_TIMES, ROAD_RESCUE, ROAD_RESCUE_TIMES, BOUTIQUE,\n" +
                "              ARTICLES_FOR_DAILY_USE, TECHNICAL_INSPECTION, INFORMATION_SERVICE,E_MAINTAIN,\n" +
                "              U.STORED_VALUE_RETAIL, INSURANCE_ORDER_AMT, USER_CAR_FEE, FN_COMMISSION,\n" +
                "              FN_SETTLE_EXPENS,OUR_LOSS_COLLECTION,INNER_LOSS_COLLECTION , CROSS_COMPANY_INCOME,CROSS_COMPANY_PAY*-1 CROSS_COMPANY_PAY,SPRAY_PAINT_NUMBER,ANNUAL_AUDIT,\n" +
                "              (SALE_MONEY+INNER_LOSS_COLLECTION-FN_SETTLE_EXPENS) SERVICE_PERFORMANCE_MONEY\n" +
                "        FROM RCL_DAILYSALESREPORT U\n" +
                "      WHERE 1 = 1 ");
        if (!param.containsKey("DATE_START") || !param.containsKey("DATE_END")) {
            throw new RuntimeException("日期为必输项！");
        }
        sb.append("  AND DATE_OF_STATISTICS >= to_date(:DATE_START, 'yyyy-MM-dd')\n" +
                "  AND DATE_OF_STATISTICS < to_date(:DATE_END, 'yyyy-MM-dd') ");

        if (param.containsKey("DEPT_ID") && param.getString("DEPT_ID") != null) {
            sb.append("AND DEPT_ID = :DEPT_ID ");
        }
        if (param.containsKey("COMPANY_ID") && param.getString("COMPANY_ID") != null) {
            sb.append("AND COMPANY_ID = :COMPANY_ID ");
        }
        sb.append("  AND ((EXISTS(SELECT 1\n" +
                "               FROM AUT_POSTS ROLE1,\n" +
                "                    AUT_LOCAL_PERMIT ROLE2,\n" +
                "                    AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
                "               WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID\n" +
                "                         AND ROLE1.ID = :ROLE AND ROLE3.TYPE = 'pt_dept' AND ROLE3.PURPOSE = 'pp_search' AND DEPT_ID =\n" +
                "                     ROLE3.ORG_ID\n" +
                "            ) OR (SELECT NVL(COUNT(1), 0)\n" +
                "                  FROM AUT_POSTS ROLE1,\n" +
                "                       AUT_LOCAL_PERMIT ROLE2,\n" +
                "                       AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
                "                  WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID\n" +
                "                    AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID\n" +
                "                    AND ROLE1.ID\n" +
                "                      = :ROLE\n" +
                "                    AND ROLE3.TYPE = 'pt_dept'\n" +
                "                    AND ROLE3.PURPOSE = 'pp_search') = 0))");
        sb.append(")");
        return toJSONArray(find(sb.toString(), param));
    }


}
